/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package yuhuibear.poat.query;

import java.util.Date;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import yuhuibear.poat.exception.MissDefinition;
import yuhuibear.poat.object.bean.PersistentClassLoader;
import yuhuibear.poat.test.entity.Log;
import yuhuibear.poat.test.entity.User;
import static org.junit.Assert.*;

/**
 * 
 * @author yuhuibear
 */
public class SQLBuilderTest {

	private static SQLBuilder sqlb;

	private static PersistentClassLoader baz;

	public SQLBuilderTest() {}

	@BeforeClass
	public static void setUpClass() throws Exception {
		baz = PersistentClassLoader.getInstance();
		System.out.println(" scanned "
				+ baz.scanPackage("yuhuibear.poat.test.entity") + " beans");
		sqlb = new SQLBuilder(baz);
	}

	@AfterClass
	public static void tearDownClass() throws Exception {}

	@Before
	public void setUp() {}

	@After
	public void tearDown() {}

	@Test
	public void testParseSelect() throws Exception {
		SQLBuilder instance = sqlb;
		ObjectQuery sql = new ObjectQuery(baz, Query.SELECT);
		sql.setTarget(User.class, true);

		// sql.addFrom(Log.class);
		sql.and("User.name = ?");
		sql.and("User.credite >10 ");

		sql.and("Log.ownerId = User.id");

		String expResult = "select distinct users.* from users, log222s where users.name = ? and users.credite >10 and log222s.owner_id = users.id";
		String result = instance.getSQL(sql);
		assertEquals(expResult, result);

		sql.and("order by asc User.id");
		sql.addAs("User.id", "NO.");
		sql.addAs("Log.dsp", "comment");
		sql.addAs("User.name", "name");
		expResult = "select distinct on (users.id) users.id as NO., log222s.dsp as comment, users.name as name "
				+ "from users, log222s "
				+ "where users.name = ? and users.credite >10 and log222s.owner_id = users.id "
				+ "order by asc users.id";
		result = instance.getSQL(sql);
		assertEquals(expResult, result);
	}

	@Test
	public void testSelectGroup() throws Exception {
		SQLBuilder instance = sqlb;
		ObjectQuery sql = new ObjectQuery(baz, Query.SELECT);
		sql.setTarget(User.class, true);

		// sql.addFrom(Log.class);
		sql.and("User.name = ?");
		sql.and("User.credite >10 ");

		sql.and("Log.ownerId = User.id");

		sql.and("order by asc User.id");
		sql.and("group by User.id having count(User.id) >0");
		sql.addAs("User.id", "NO.");
		sql.addAs("Log.dsp", "comment");
		sql.addAs("User.name", "name");
		String expResult = "select distinct on (users.id) users.id as NO., log222s.dsp as comment, users.name as name "
				+ "from users, log222s "
				+ "where users.name = ? and users.credite >10 and log222s.owner_id = users.id "
				+ "order by asc users.id group by users.id having count(users.id) >0";
		String result = instance.getSQL(sql);
		assertEquals(expResult, result);
	}

	@Test
	public void testParseDistinctSelect() throws Exception {
		SQLBuilder instance = sqlb;
		ObjectQuery sql = new ObjectQuery(baz, Query.SELECT);
		sql.setTarget(User.class, true);

		sql.and("User.name = ?");
		sql.and("Log.ownerId = User.id");

		String expResult = "select distinct users.* from users, log222s where users.name = ? and log222s.owner_id = users.id";
		String result = instance.getSQL(sql);
		assertEquals(expResult, result);

		sql.and("order by User.id desc");
		expResult = "select distinct on (users.id) users.* "
				+ "from users, log222s "
				+ "where users.name = ? and log222s.owner_id = users.id "
				+ "order by users.id desc";
		result = instance.getSQL(sql);
		assertEquals(expResult, result);

	}

	@Test
	public void testParseInsert() throws Exception {
		System.out.print("insert :");
		SQLBuilder instance = sqlb;
		ObjectQuery sql = new ObjectQuery(baz, ObjectQuery.INSERT);

		sql.setTarget(User.class, false);
		sql.addFrom(Log.class);
		sql.and("User.name = ?");
		sql.and("User.credite >10 ");
		sql.and("Log.ownerId = User.id");

		String expResult = "insert into users (id, name, data, commentby, credite ) values (?, ?, ?, ?, ?)";
		String result = instance.getSQL(sql);
		assertEquals(expResult, result);

		sql.setTarget(Log.class, false);

		Log log = new Log();
		log.setName("log insert test");
		log.setTime(new Date());
		log.setDsp("aaaaaaaaaaaa");
		expResult = "insert into log222s (time, name, owner_id, date, dsp ) values (?, ?, ?, ?, ?)";
		result = instance.getSQL(sql, log).sql;
		assertEquals(expResult, result);
		System.out.println("succeed");
	}

	@Test
	public void testParseDelete() throws Exception {
		System.out.print("delete :");
		SQLBuilder instance = sqlb;
		ObjectQuery sql = new ObjectQuery(baz, Query.DELETE);

		sql.setTarget(User.class);

		String expResult = ObjectQuery.CMD[ObjectQuery.DELETE] + "users"
				+ SQLBuilder.WHERE + "id = ? ";
		String result = instance.getSQL(sql);
		assertEquals(expResult, result);

		sql.and("User.id = 1");
		expResult = ObjectQuery.CMD[ObjectQuery.DELETE] + "users"
				+ SQLBuilder.WHERE + "users.id = 1";
		result = instance.getSQL(sql);
		assertEquals(expResult, result);

		ObjectQuery osql = new ObjectQuery(baz, Query.DELETE);
		osql.setTarget(Log.class);
		osql.setConditions(new String[] { "*" });
		expResult = ObjectQuery.CMD[ObjectQuery.DELETE] + "log222s";
		result = sqlb.getSQL(osql);
		assertEquals(expResult, result);

		System.out.println("succeed");
	}

	@Test
	public void testParseUpdate() throws Exception {
		System.out.print("update :");
		SQLBuilder instance = sqlb;
		ObjectQuery sql = new ObjectQuery(baz, Query.UPDATE);
		sql.setTarget(User.class);

		String expResult = "update users set id = ?, name = ?, data = ?, commentby = ?, credite = ? where id = ?";
		String result = instance.getSQL(sql);
		assertEquals(expResult, result);

		sql.setUpdateAttributes("testId = 999");
		sql.setUpdateAttributes("nameDeleted = 'ffFfff'");

		String[] cnds = new String[] { "cedite > 1000", "enable = true" };
		sql.setConditions(cnds);

		expResult = "update users set test_id = 999, name_deleted = 'ffFfff' where cedite > 1000 and enable = true";
		result = instance.getSQL(sql);
		assertEquals(expResult, result);

		sql = new ObjectQuery(baz, Query.UPDATE);
		sql.setTarget(Log.class);
		sql.setUpdateAttributes("Log.id = 'ssss'");
		sql.setConditions(new String[] { "*" });
		result = sqlb.getSQL(sql);
		expResult = "update log222s set id = 'ssss'";
		assertEquals(expResult, result);

		System.out.println("succeed");
	}

	@Test
	public void testParseFreeBuild() throws Exception {
		System.out.print("free build :");
		SQLBuilder instance = sqlb;
		ObjectQuery sql = new ObjectQuery(baz, Query.SELECT);
		sql.setTarget(Log.class, false);
		sql.addAs("SumLogs(User.id)", "log_cnt");
		sql.addAs("sumXlogs(Log.ownerId)", "tes");

		sql.and("User.name = CallName(Log.id)");
		sql.and("User.cmt >10 ");
		sql.and("Log.ownerId = User.id");

		String expResult = "select SumLogs(users.id) as log_cnt, sumXlogs(log222s.owner_id) as tes "
				+ "from users, log222s "
				+ "where users.name = CallName(log222s.id) and users.commentby >10 and log222s.owner_id = users.id";

		String result = instance.getSQL(sql);
		assertEquals(expResult, result);

		try {
			sql.and("User.name = CallName(User.id)");
			result = instance.getSQL(sql);
		} catch (MissDefinition mr) {
			System.out.print(" exception test, passed. ");
		}
		System.out.println("succeed");
	}

}
